<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/10/24
 * Time: 14:20
 */

namespace app\admin\controller;
use think\Controller;
use think\Db;
class Excel extends Controller
{



//    导入
    function run_in(){
        import('Classes.PHPExcel', EXTEND_PATH);
//        vendor("PHPExcel.PHPExcel"); //方法一
        $objPHPExcel = new \PHPExcel();
        //获取表单上传文件
        $file = request()->file('excel');
//        文件大小size




        $info = $file->validate(['size'=>11111111111111,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH.'public'.DS . 'excel');





        if($info){
            $exclePath = $info->getSaveName();  //获取文件名
            $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath;   //上传文件的地址
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
            echo "<pre>";






            $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
            array_shift($excel_array);
            array_shift($excel_array);  //删除第一个数组(标题);
//            unset($excel_array[0]);
//            unset($excel_array[1]);

            $data = [];
            $i=0;
//            var_dump($excel_array) ;exit();

            foreach($excel_array as $k=>$v) {
                $data[$k]['name'] = $v[1];
                $data[$k]['pass'] = $v[2];
                $i++;
            }

            $success=Db::name('ping')->insertAll($data); //批量插入数据





            $error=$i-$success;
            echo "总{$i}条，成功{$success}条，失败{$error}条。";
        }else{
            // 上传失败获取错误信息
            echo $file->getError();
        }
    }












//    导出表格
//调用
    function run_out(){
        $excelname = 'mdm';
		$job_group_id = Request()->param('job_group_id');
        $data = Db::name('job')->where('job_group_id',$job_group_id)->select();
		foreach($data as $k=>$v){
			switch($v['job_getjob_sn']){
				case 1:
				for($i = 0 ;$i < count($data);$i++){
					$data[$i]['job_getjob_sn'] = '结业';
					$data[$i]['job_getjob_time'] = date('Y/m/d',$v['job_getjob_time']);
				}
				break;
				case 2:
				for($i = 0 ;$i < count($data);$i++){
					$data[$i]['job_getjob_sn'] = '求职中';
					$data[$i]['job_getjob_time'] = date('Y/m/d',$v['job_getjob_time']);
				}
				break;
				case 3:
				for($i = 0 ;$i < count($data);$i++){
					$data[$i]['job_getjob_sn'] = '就业';
					$data[$i]['job_getjob_time'] = date('Y/m/d',$v['job_getjob_time']);
				}
				break;
				case 4:
				for($i = 0 ;$i < count($data);$i++){
					$data[$i]['job_getjob_sn'] = '自主就业';
					$data[$i]['job_getjob_time'] = date('Y/m/d',$v['job_getjob_time']);
				}
				break;
			}
		}
		$config  = array(
            array('job_id','序列号'),
            array('job_name','姓名'),
            array('job_company','就业公司'),
			array('job_person_money','就业薪资'),
			array("job_getjob_time",'入职时间'),
            array('job_work_name','岗位名',),
            array('job_work_text','备注'),
			array('job_getjob_sn','状态'),
        );
        $this->getexcels($excelname,$config,$data);
    }
//    封装
    function getexcels($excelname,$config,$data){
//       extend下的 phpexcel  文件夹名    PHPExcel 文件名
//        import('PHPExcel', EXTEND_PATH);
        vendor('PHPExcel');
        $excel = new \PHPExcel();
//        $data  数据库传输的数据
//        $excelname  拼接excel名字
//        $config    相关配置\
        $cellNum = count($config);
        $dataNum = count($data);

//        是你的excel对应的所有列
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
//  合并列  A1起始的位置   $cellName[$cellNum-1]  最后一列    1合并成几列
        $excel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格


//        往这一列放入数据
        $excel->setActiveSheetIndex(0)->setCellValue('A1', '铭代码学生就业表');


        for($i=0;$i<$cellNum;$i++){
            $excel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $config[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8  拼接数据
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $excel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $data[$i][$config[$j][0]]);
            }
        }
        ob_end_clean();//这一步非常关键，用来清除缓冲区防止导出的excel乱码
        $xlsTitle = iconv('utf-8', 'gb2312', $excelname);//文件名称


//        excel 名字
        $fileName = $excelname.date('_YmdHis').rand(111111,999999);//or $xlsTitle 文件名称可根据自己情况设定



        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//"xls"参考下一条备注

        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');//"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls
        $objWriter->save('php://output');
        exit;
    }

}